package cn.com.easttech.gofishing.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.DatabaseErrorHandler;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;

import cn.com.easttech.gofishing.R;

/**
 * Created by jianl on 2017/12/21.
 */

public class DBHelper extends SQLiteOpenHelper {

    private static final String DROP_COMPANY = "drop table if exists COMPANY";

    private static final String CREATE_COMPANY =
                    "CREATE TABLE COMPANY ("
                    + "    UUID varchar(100) NOT NULL,"
                    + "    NAME varchar(100) DEFAULT NULL,"
                    + "    SORT_NO int(11) DEFAULT NULL,"
                    + "    UPDATED_DATE date DEFAULT NULL,"
                    + "    PRIMARY KEY (UUID)"
                    + ")";

    private static final String DROP_PRODUCT_CATALOG = "drop table if exists PRODUCT_CATALOG";
    private static final String CREATE_PRODUCT_CATALOG =
                    "CREATE TABLE PRODUCT_CATALOG ("
                    + "  UUID varchar(100) NOT NULL,"
                    + "  NAME varchar(100) DEFAULT NULL,"
                    + "  SUPER_CATALOG_ID varchar(100) DEFAULT NULL,"
                    + "  COMPANY_UUID varchar(100) DEFAULT NULL,"
                    + "  SORT_NO bigint(255) DEFAULT NULL,"
                    + "  UPDATED_DATE date DEFAULT NULL,"
                    + "  PRIMARY KEY (UUID)"
                    + ")";

    private static final String DROP_PRODUCT_INFO = "drop table if exists PRODUCT_INFO";
    private static final String CREATE_PRODUCT_INFO =
                    "CREATE TABLE PRODUCT_INFO ("
                    + "    UUID varchar(100) NOT NULL,"
                    + "    NAME varchar(200) DEFAULT NULL,"
                    + "    PICTURE varchar(200) DEFAULT NULL,"
                    + "    TEXT_DESC varchar(4000) DEFAULT NULL,"
                    + "    VIDEO_DESC varchar(200) DEFAULT NULL,"
                    + "    PICTURE_DESC varchar(200) DEFAULT NULL,"
                    + "    CATALOG_UUID varchar(100) DEFAULT NULL,"
                    + "    COMPANY_UUID varchar(100) DEFAULT NULL,"
                    + "    SORT_NO bigint(255) DEFAULT NULL,"
                    + "    NEW_PRODUCT varchar(100) DEFAULT NULL,"
                    + "    UPDATE_DATE date DEFAULT NULL,"
                    + "    PRIMARY KEY (UUID)"
                    + ")";

    private Context context;

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        this(context, name, factory, version, null);
    }

    public DBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version, DatabaseErrorHandler errorHandler) {
        super(context, name, factory, version, errorHandler);

        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        Log.d("DBHelper", "onCreate");
        db.execSQL(CREATE_COMPANY);
        db.execSQL(CREATE_PRODUCT_CATALOG);
        db.execSQL(CREATE_PRODUCT_INFO);

        try {
            InputStream is = context.getResources().openRawResource(R.raw.data);
            BufferedReader reader = new BufferedReader(new InputStreamReader(is));

            String str = null;
            while ((str = reader.readLine()) != null) {
                Log.d("DBHelper", str);
            }

        } catch (Exception e) {
            Log.e("DBHelper", e.getMessage());
        }

        ContentValues values = new ContentValues();
        values.put("UUID", "1");
        values.put("NAME", "法雨");
        values.put("SORT_NO", "10");
        db.insert("COMPANY", null, values);

        values.clear();
        values.put("UUID", "2");
        values.put("NAME", "钓鱼郎");
        values.put("SORT_NO", "20");
        db.insert("COMPANY", null, values);

        values.clear();
        values.put("UUID", "1");
        values.put("NAME", "鱼竿类");
        values.put("SUPER_CATALOG_ID", "");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "10");
        values.put("UPDATED_DATE", "");
        db.insert("PRODUCT_CATALOG", null, values);

        values.clear();
        values.put("UUID", "2");
        values.put("NAME", "新品 竹-鲫竿 3.5h");
        values.put("SUPER_CATALOG_ID", "1");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "10");
        values.put("UPDATED_DATE", "");
        db.insert("PRODUCT_CATALOG", null, values);

        values.clear();
        values.put("UUID", "3");
        values.put("NAME", "新品 菊 5h");
        values.put("SUPER_CATALOG_ID", "1");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "20");
        values.put("UPDATED_DATE", "");
        db.insert("PRODUCT_CATALOG", null, values);

        values.clear();
        values.put("UUID", "1");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi1.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi5");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi2.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "10");
        values.put("NEW_PRODUCT", "1");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "2");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi2.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi6");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi3.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "20");
        values.put("NEW_PRODUCT", "1");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "3");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi3.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi7");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi4.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "30");
        values.put("NEW_PRODUCT", "1");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "4");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi4.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi8");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi5.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "40");
        values.put("NEW_PRODUCT", "1");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "5");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi5.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi9");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi6.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "50");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "6");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi6.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi10");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi7.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "60");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "7");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi7.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi11");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi8.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "70");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "8");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi8.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi12");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi9.jpg");
        values.put("CATALOG_UUID", "2");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "80");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "9");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi9.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi13");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi10.jpg");
        values.put("CATALOG_UUID", "3");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "90");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);

        values.clear();
        values.put("UUID", "10");
        values.put("NAME", "快乐垂钓剑冈名满天下wifi5手竿超轻超硬28调鱼竿钓鱼竿台钓竿");
        values.put("PICTURE", "http://www.china-fishboy.com/pi10.jpg");
        values.put("TEXT_DESC", "日本东丽碳布 名满天下 wifi14");
        values.put("VIDEO_DESC", "http://www.china-fishboy.com/video.mp4");
        values.put("PICTURE_DESC", "http://www.china-fishboy.com/pi11.jpg");
        values.put("CATALOG_UUID", "3");
        values.put("COMPANY_UUID", "1");
        values.put("SORT_NO", "100");
        values.put("NEW_PRODUCT", "0");
        values.put("UPDATE_DATE", "");
        db.insert("PRODUCT_INFO", null, values);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL(DROP_COMPANY);
        db.execSQL(DROP_PRODUCT_CATALOG);
        db.execSQL(DROP_PRODUCT_INFO);
        onCreate(db);
    }
}
